<?php

class Sgit_Util_DataTable
{
    function __construct($sTable,$aColumns)
    {
        $this->db = Zend_Db_Table::getDefaultAdapter();
        $this->get= Zend_Controller_Front::getInstance()->getRequest()->getQuery();


        if (isset($this->get['sColumns']) && $this->get['sColumns'])
        {
            $sColumns = $this->get['sColumns'];
            $aColumns = explode(',', $sColumns);
        }
        
        /* Indexed column (used for fast and accurate table cardinality) */
        $sIndexColumn = "id";

        $this->select = $this->db->select()->from($sTable, 
            array(new Zend_Db_Expr('SQL_CALC_FOUND_ROWS id'), '*')
        );
        
        $this->order();
        $this->limit();
        $this->filter();
    }



    function sql()
    {
       $sql['order'] =      $this->select->getPart( Zend_Db_Select::ORDER);
       $sql['where']  =     $this->select->getPart( Zend_Db_Select::WHERE);
       $sql['limitcount'] = $this->select->getPart( Zend_Db_Select::LIMIT_COUNT);
       $sql['limitoffset']= $this->select->getPart( Zend_Db_Select::LIMIT_OFFSET);

       return $sql;
    }


    function limit()
    {
        /*
        * Paging
        */

        if (isset($this->get['iDisplayStart']) && $this->get['iDisplayLength'] != '-1')
        {
            $this->select->limit($this->get['iDisplayStart'],$this->get['iDisplayLength']);
        }
        
    }

    function order()
    {
        /*
        * Ordering
        */
        
        if (isset($this->get['iSortCol_0']))
        {
            for ($i = 0; $i < intval($this->get['iSortingCols']); $i++)
            {
                if ($this->get['bSortable_' . intval($this->get['iSortCol_' . $i])] == "true")
                {
                    $sOrder = $this->aColumns[intval($this->get['iSortCol_' . $i])] . "  " . mysql_real_escape_string($this->get['sSortDir_' . $i]);
                    
                    $this->select->order($sOrder);
                }
            }

        }
    }

    function filter()
    {
        /*
        * Filtering
        * NOTE this does not match the built-in DataTables filtering which does it
        * word by word on any field. It's possible to do here, but concerned about efficiency
        * on very large tables, and MySQL's regex functionality is very limited
        */

        if (isset($this->get['sSearch']) && $this->get['sSearch'] != "")
        {
            $sWhere = "WHERE (";

            for ($i = 0; $i < count($this->aColumns); $i++)
            {

                if (isset($this->get['sSortable_' . $i]) && $this->get['sSortable_' . $i] == "true")
                {
                    $this->select->orWhere(array("{$this->aColumns[$i]} LIKE '?' "=>$this->get['sSearch']));
                }
            }
        }


        /* Individual column filtering */
        for ($i = 0; $i < count($this->aColumns); $i++)
        {
            if (isset($this->get['bSearchable_' . $i]) && $this->get['bSearchable_' . $i] == "true" && $this->get['sSearch_' . $i] != '')
            {
                 $this->select->orWhere(array("{$this->aColumns[$i]} LIKE '?' "=>$this->get['sSearch_' . $i]));
            }
        }


    }

}
